﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Configuration;
using System.Reflection.Emit;
using System.Reflection;
using System.Data;

namespace Commons
{
    public class DbHelper
    {
        private static DbHelper helper;

        private ArrayList valueTypeList = new ArrayList();

        public ArrayList ValueTypeList 
        {
            get 
            {
                return this.valueTypeList;
            } 
        }

        public static DbHelper getHelper()
        {
            return DbHelper.getHelper(null);
        }

        public static DbHelper getHelper(string connString)
        {
            if (DbHelper.helper == null)
            {
                DbHelper.helper = new DbHelper(connString);
            }
            return DbHelper.helper;
        }

        private string connectString;
        
        public string ConnectString
        {
            get { return connectString; }
            set { connectString = value; }
        }
        private SqlConnection connection;

        public SqlConnection Connection
        {
            get { return connection; }
            set { connection = value; }
        }

        /// <summary>
        /// 类构造器
        /// </summary>
        private DbHelper()
        {
            Initialize(null);
        }

        /// <summary>
        /// 类构造器
        /// </summary>
        /// <param name="connString">链接字符串</param>
        private DbHelper(string connString)
        {
            Initialize(connString);
            if (valueTypeList == null) valueTypeList  = new ArrayList();
            valueTypeList.Add(typeof(int));
            valueTypeList.Add(typeof(double));
            valueTypeList.Add(typeof(string));
            valueTypeList.Add(typeof(bool));
            valueTypeList.Add(typeof(long));
            valueTypeList.Add(typeof(decimal));

        }

        /// <summary>
        /// 初使化方法
        /// </summary>
        private void Initialize()
        {
            Initialize(null);
        }

        /// <summary>
        /// 初使化方法
        /// </summary>
        /// <param name="connString">链接字符串</param>
        private void Initialize(String connString)
        {
            if (!String.IsNullOrEmpty(connString))
            {
                this.connectString = connString;
            }
            else
            {
                this.connectString = ConfigurationManager.ConnectionStrings["SQLSERVER"].ConnectionString;
            }
            using (this.connection = new SqlConnection(connectString))
            {
                this.connection.Open();
            }
        }

        /// <summary>
        /// 获得一个已设置好参数的SqlCommand
        /// </summary>
        /// <param name="sql">SQL字符串</param>
        /// <param name="paras">SQL参数</param>
        /// <returns>SqlCommand</returns>
        public SqlCommand GetPreparedCommand(string sql, params object[] parm)
        {
            SqlCommand scb = new SqlCommand();
            if (this.connection != null && this.connection.State != System.Data.ConnectionState.Open)
            {
                this.connection.ConnectionString = this.connectString;
                this.connection.Open();
            }
            scb.Connection = this.connection;
            scb.CommandText = sql;
            scb.CommandType = System.Data.CommandType.Text;
            int count = 0;
            foreach (string name in GetParmeterNames(sql))
            {
                scb.Parameters.Add(new SqlParameter(name, parm[count] != null ? parm[count] : DBNull.Value));
                count++;
            }
            return scb;
        }

        /// <summary>
        /// 获得一个已设置好参数的SqlCommand
        /// </summary>
        /// <param name="sql">SQL字符串</param>
        /// <param name="paras">SQL参数</param>
        /// <returns>SqlCommand</returns>
        public DataSet ExcuteProcudure(string procudre, Hashtable param)
        {
            SqlCommand scb = new SqlCommand();
            if (this.connection != null && this.connection.State != System.Data.ConnectionState.Open)
            {
                this.connection.ConnectionString = this.connectString;
                this.connection.Open();
            }
            scb.Connection = this.connection;
            scb.CommandText = procudre;
            scb.CommandType = System.Data.CommandType.StoredProcedure;
            foreach (string key in param.Keys)
            {
                scb.Parameters.Add(new SqlParameter(key, param[key]));
            }
            DataSet ret = new DataSet();
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = scb;
            sda.Fill(ret);
            return ret;
        }

        /// <summary>
        /// 执行查询，返回SqlDataReader
        /// </summary>
        /// <param name="sql">SQL字符串</param>
        /// <param name="paras">SQL参数</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader ExcuteQuery(string sql, params object[] parm)
        {
            return GetPreparedCommand(sql, parm).ExecuteReader();
        }

        /// <summary>
        /// 执行查询，返回DataTable
        /// </summary>
        /// <param name="sql">SQL字符串</param>
        /// <param name="paras">SQL参数</param>
        /// <returns>DataTable</returns>
        public DataTable ExcuteDataTableQuery(string sql, params object[] parm)
        {
            SqlCommand scb = GetPreparedCommand(sql, parm);
            SqlDataAdapter adapter = new SqlDataAdapter(scb);
            DataTable ret = new DataTable();
            adapter.Fill(ret);
            return ret;
        }

        /// <summary>
        /// 执行查询，返回DataSet
        /// </summary>
        /// <param name="sql">SQL字符串</param>
        /// <param name="paras">SQL参数</param>
        /// <returns>DataSet</returns>
        public DataSet ExcuteDataSetQuery(string sql, params object[] parm)
        {
            SqlCommand scb = GetPreparedCommand(sql, parm);
            SqlDataAdapter adapter = new SqlDataAdapter(scb);
            DataSet ret = new DataSet();
            adapter.Fill(ret);
            return ret;
        }

        /// <summary>
        /// 找到并返回SQL中所有参数名称
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <returns>List</returns>
        private List<string> GetParmeterNames(string sql)
        {
            List<string> names = new List<string>();
            Regex reg = new Regex(@"\@[a-zA-Z0-9]+");
            foreach(Match match in reg.Matches(sql))
            {
                if (!names.Contains(match.Value))
                {
                    names.Add(match.Value);
                }
                
            }
            return names;
        }

        /// <summary>
        /// 用List生成DataTable
        /// </summary>
        /// <param name="list">List</param>
        /// <returns>DataTable</returns>
        public DataTable setListToDataTable<T>(List<T> list)
        {
            DataTable ret = new DataTable();
            PropertyInfo[] prop = typeof(T).GetProperties(BindingFlags.Public |
                                        BindingFlags.Static |
                                        BindingFlags.NonPublic |
                                        BindingFlags.Instance);

            foreach (PropertyInfo info in prop)
            {
                DataColumn dc = new DataColumn(info.Name, info.PropertyType);
                ret.Columns.Add(dc);
            }
            foreach (T val in list)
            {
                DataRow dr = ret.NewRow();
                foreach (PropertyInfo info in prop)
                {
                    dr[info.Name] = info.GetValue(val, null);
                }
                ret.Rows.Add(dr);
            }
            return ret;

        }

        /// <summary>
        /// 用DataTable生成List
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <returns>List</returns>
        public List<T> setDataTableToList<T>(DataTable table)
        {
            List<T> ret = new List<T>();
            PropertyInfo[] prop = typeof(T).GetProperties(BindingFlags.Public |
                                        BindingFlags.Static |
                                        BindingFlags.NonPublic |
                                        BindingFlags.Instance);

            foreach (DataRow dr in table.Rows)
            {
                T val = (T)Activator.CreateInstance(typeof(T));
                foreach (PropertyInfo info in prop)
                {
                    info.SetValue(val, dr[info.Name], null);
                }
                ret.Add(val);
            }
            return ret;
        }

        /// <summary>
        /// 将SqlDataReader中的所有的行设置到指定泛型类的实例中，并返回该实例的列表（List）
        /// </summary>
        /// <param name="reader">SqlDataReader</param>
        /// <returns>列表List</returns>
        public List<T> setToEntityList<T>(SqlDataReader reader)
        {
            List<T> list = new List<T>();
            if (reader.IsClosed)
            {
                throw new Exception("SqlDataReader is closed!");
            }
            while (!reader.IsClosed)
            {
                T val = this.setToEntity<T>(reader);
                if (!IsDefaultValue(val))
                {
                    list.Add(val);
                }

            }
            return list;
        }

        /// <summary>
        /// 将SqlDataReader中的值设置到指定泛型类的实例中，并返回该实例
        /// </summary>
        /// <param name="reader">SqlDataReader</param>
        /// <returns>指定泛型类的实例</returns>
        public T setToEntity<T>(SqlDataReader reader)
        {
            Hashtable propMap = new Hashtable();
            List<string> columnNames = new List<string>();

            if (reader.Read())
            {

                if (valueTypeList.Contains(typeof(T)))
                {
                    T ret = (T)reader.GetValue(0);
                    if (!reader.Read()) reader.Close();
                    return ret;
                }
                PropertyInfo[] prop = typeof(T).GetProperties(BindingFlags.Public |
                                        BindingFlags.Static |
                                        BindingFlags.NonPublic |
                                        BindingFlags.Instance);

                foreach (PropertyInfo info in prop)
                {
                    DbColumn col = (DbColumn)Attribute.GetCustomAttribute(info, typeof(DbColumn));
                    if (col != null)
                    {
                        columnNames.Add(col.ColumnID.ToLower());
                        propMap.Add(col.ColumnID.ToLower(), info);
                    }
                    else
                    {
                        columnNames.Add(info.Name.ToLower());
                        propMap.Add(info.Name.ToLower(), info);
                    }
                }
                T obj = (T)Activator.CreateInstance(typeof(T));
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    string fieldname = reader.GetName(i).ToLower();
                    if (columnNames.Contains(fieldname))
                    {
                        PropertyInfo info = (PropertyInfo)propMap[fieldname];
                        info.SetValue(obj, reader.GetValue(i).Equals(DBNull.Value) ? null : reader.GetValue(i), null);
                    }
                }
                if (!reader.Read()) reader.Close();
                return obj;
            }
            else
            {
                reader.Close();
                return default(T); ;
            }
        }

        /// <summary>
        /// 基础类型初始值比较
        /// </summary>
        /// <param name="val">泛型值</param>
        /// <returns>true:是，false:否</returns>
        private bool IsDefaultValue<T>(T val)
        {
            Object obj = val;
            if(typeof(T)==typeof(int))
            {
                    int intValue = default(int);
                    return intValue == (int)obj;
            }
            if(typeof(T)==typeof(double))
            {
                    double dbValue = default(double);
                    return dbValue == (double)obj;
            }
            if(typeof(T)==typeof(string))
            {
                    string stringVal = default(string);
                    return stringVal == (string)obj;
            }
            if(typeof(T)==typeof(bool))
            {
                    bool boolVal = default(bool);
                    return boolVal == (bool)obj;
            }
            if(typeof(T)==typeof(long))
            {
                    long longVal = default(long);
                    return longVal == (long)obj;
            }
            if(typeof(T)==typeof(decimal))
            {
                    decimal decVal = default(decimal);
                    return decVal == (decimal)obj;
            }
            return false;
        }

        /// <summary>
        /// 执行非查询语句，返回影响行数
        /// </summary>
        /// <param name="sql">SQL文字列</param>
        /// <param name="param">参数列表</param>
        /// <returns>影响行数</returns>
        public int ExcuteUpdate(string sql,params object[] param)
        {
            return this.GetPreparedCommand(sql, param).ExecuteNonQuery();
        }

        /// <summary>
        /// 将实体类插入到数库中
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <returns>影响行数</returns>
        public int saveEntity(Object entity)
        {
            string tblName = "";
            DbTable tbl = (DbTable)Attribute.GetCustomAttribute(entity.GetType(), typeof(DbTable));
            tblName = (tbl != null ? tbl.Name : entity.GetType().Name);
            List<string> columnNames = new List<string>();
            List<object> columnValues = new List<object>();
            StringBuilder sba = new StringBuilder();
            StringBuilder sbb = new StringBuilder();
            string comma = "";
            sba.AppendLine("INSERT INTO " + tblName + "( ");

            PropertyInfo[] prop = entity.GetType().GetProperties(BindingFlags.Public |
                                        BindingFlags.Static |
                                        BindingFlags.NonPublic |
                                        BindingFlags.Instance |
                                        BindingFlags.DeclaredOnly);

            foreach (PropertyInfo info in prop)
            {
                DbColumn col = (DbColumn)Attribute.GetCustomAttribute(info, typeof(DbColumn));
                
                if (col != null)
                {
                    if (col.IsIdentity != true)
                    {
                        columnNames.Add(col.ColumnID);
                        columnValues.Add(info.GetValue(entity, null));
                        sba.AppendLine("   " + comma + " " + col.ColumnID);
                        sbb.AppendLine("   " + comma + " @" + col.ColumnID);
                        comma = ",";
                    }
                }
                else
                {
                    columnNames.Add(info.Name);
                    columnValues.Add(info.GetValue(entity, null));
                    sba.AppendLine("   " + comma + " " + info.Name);
                    sbb.AppendLine("   " + comma + " @" + info.Name);
                    comma = ",";
                }
                
                
            }
            sba.AppendLine("    ) VALUES ( ");
            sba.Append(sbb);
            sba.AppendLine("   )");


            return this.ExcuteUpdate(sba.ToString(), columnValues.ToArray());
        }

        /// <summary>
        /// 将实体类中的内容更新到数库中
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <returns>影响行数</returns>
        public int updateEntity(Object entity)
        {
            string tblName = "";
            DbTable tbl = (DbTable)Attribute.GetCustomAttribute(entity.GetType(), typeof(DbTable));
            tblName = tbl != null ? tbl.Name : entity.GetType().Name;
            List<string> columnNames = new List<string>();
            List<object> columnValues = new List<object>();
            List<object> whereValues = new List<object>();
            StringBuilder sba = new StringBuilder();
            StringBuilder sbb = new StringBuilder();
            string comma = "";
            string and = "  ";
            sba.AppendLine("UPDATE " + tblName + " SET ");

            PropertyInfo[] prop = entity.GetType().GetProperties(BindingFlags.Public |
                                        BindingFlags.Static |
                                        BindingFlags.NonPublic |
                                        BindingFlags.Instance |
                                        BindingFlags.DeclaredOnly);

            foreach (PropertyInfo info in prop)
            {
                DbColumn col = (DbColumn)Attribute.GetCustomAttribute(info, typeof(DbColumn));
                if (col != null)
                {
                    if (!col.IsIdentity)
                    {
                        columnNames.Add(col.ColumnID);
                        sba.AppendLine("   " + comma + " " + col.ColumnID + "= @" + col.ColumnID);
                        comma = ",";
                        columnValues.Add(info.GetValue(entity, null));
                    }
                    if (col.IsKey)
                    {
                        sbb.AppendLine("  " + and + col.ColumnID + "= @" + col.ColumnID);
                        and = " AND ";
                        whereValues.Add(info.GetValue(entity, null));
                    }
                }
            }
            sba.AppendLine(" WHERE ");
            sba.Append(sbb);
            columnValues.AddRange(whereValues);

            return this.ExcuteUpdate(sba.ToString(), columnValues.ToArray());
        }
    }
}
